SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE [dbo].[SP_TCMOVI_BACO_Q01]        
/*---------------------------------------------------------*/        
/*---Empresa              : OFISIS S.A.                  --*/        
/*---Cliente              : OFISIS S.A.                  --*/        
/*---Sistema              : Tesorer­a                    --*/        
/*---M"dulo               : Empresa                      --*/        
/*---Programa             : Conciliaci½n Bancaria Auromÿtica --*/        
/*---Script               : ttproc_i.sql                 --*/        
/*---Nombre SP            : SP_TCMOVI_BACO_Q01           --*/        
/*---Desarrollado por     : Reynaldo Menacho L.             --*/        
/*---Fecha Creaci"n       : 25/11/2003                   --*/        
/*---Base Datos           : Microsoft SQL                --*/        
/*---Versi"n              : 2000                          --*/        
/*---Invoca a SP          :                              --*/        
/*---------------------------------------------------------*/        
/*---------------------------------------------------------*/        
/*---Modificado 1 por     :                              --*/        
/*---Fecha Modificaci"n   :                              --*/         
/*---Detalle Modificaci"n :                              --*/        
/*---------------------------------------------------------*/        
        
/*---------------------------------------------------------*/        
/*---Comentarios                                         --*/        
/*---                                                    --*/        
/*---                                                    --*/        
/*---------------------------------------------------------*/        
        
/*---------------------------------------------------------*/        
/*--- Drop Proc SP_TCMOVI_BACO_Q01                        --*/        
/*--- Grant all on SP_TCMOVI_BACO_Q01 to public           --*/         
/*---  SP_TCMOVI_BACO_Q01 '01', 'SUD', '001-0170076', '2006', '01'       --*/        
/*---------------------------------------------------------*/        
@ISCO_EMPR TD_VC_002,        
@ISCO_BANC TD_VC_003,        
@ISNU_CNTA_BANC TD_VC_020,        
@ISAA_CONC TD_VC_004,        
@ISMM_CONC TD_VC_002        
 As        
DECLARE        
@CSCO_EMPR TD_VC_002,        
@CSCO_BANC TD_VC_003,        
@CSNU_CNTA_BANC TD_VC_020,        
@CSAA_CONC TD_VC_004,        
@CSMM_CONC TD_VC_002,        
@CSCO_TIPO_DOCU TD_VC_003,        
@CSCO_SUNA TD_VC_003,        
@CSTI_MOVI_BANC TD_VC_001,        
@CNIM_MOVI TD_NU_016_004,        
@CSSI_OPER_CONC TD_VC_001,        
@CSNU_CORR_CONC TD_VC_020,         
        
@VNNU_FETC_0001 TD_IN_001        
        
        
CREATE TABLE #TWMOVI_BACO (        
CO_EMPR   VARCHAR(002) NULL,        
CO_BANC   VARCHAR(003) NULL,        
NU_CNTA_BANC VARCHAR(020) NULL,        
AA_CONC   VARCHAR(004) NULL,        
MM_CONC   VARCHAR(002) NULL,        
SI_OPER_CONC VARCHAR(001) NULL,        
FE_OPER   DATETIME  NULL,        
NU_COMP_BANC VARCHAR(020) NULL,        
TI_MOVI_BANC VARCHAR(001) NULL,        
CO_TIPO_DOCU VARCHAR(003) NULL,        
NU_DOCU_BANC VARCHAR(020) NULL,        
NU_CORR_CONC VARCHAR(020) NULL,        
CO_SUNA   VARCHAR(003) NULL,        
AA_BNCO   VARCHAR(004) NULL,        
MM_BNCO   VARCHAR(002) NULL,        
CO_GRUP_CONC VARCHAR(020) NULL,        
IM_MOVI   NUMERIC(16,4) NULL,        
DE_OBSE   VARCHAR(100) NULL,        
SI_OPER_0001 VARCHAR(001) NULL,        
TI_MOVI_0001 VARCHAR(001) NULL,        
FE_OPER_0001 DATETIME    NULL,        
CO_TIPO_0001 VARCHAR(003) NULL,        
NU_CORR_0001 VARCHAR(020) NULL,        
CO_SUNA_0001 VARCHAR(003) NULL,        
AA_BNCO_0001 VARCHAR(004) NULL,        
MM_BNCO_0001 VARCHAR(002) NULL,        
IM_MOVI_0001 NUMERIC(16,4) NULL,        
DE_OBSE_0001 VARCHAR(100) NULL        
)        
        
        
Insert Into #TWMOVI_BACO (CO_EMPR, CO_BANC, NU_CNTA_BANC, AA_CONC, MM_CONC, FE_OPER, NU_COMP_BANC, NU_CORR_CONC,        
 TI_MOVI_BANC, IM_MOVI, AA_BNCO, MM_BNCO, CO_GRUP_CONC, CO_TIPO_DOCU, SI_OPER_CONC, CO_SUNA, NU_DOCU_BANC,        
 DE_OBSE)        
        
Select  @ISCO_EMPR, @ISCO_BANC, @ISNU_CNTA_BANC, @ISAA_CONC, @ISMM_CONC, Max(T1.FE_OPER), Max(T1.NU_COMP_BANC),  Max(T1.NU_CORR_CONC),        
 
	  Max(T1.TI_MOVI_BANC), Sum(T1.IM_MOVI),         
	  Max(T1.AA_BNCO), Max(T1.MM_BNCO), Max(T1.CO_GRUP_CONC), Max(T1.CO_TIPO_DOCU),        
	  Max(T1.SI_OPER_CONC), Max(T2.CO_SUNA), Max(T1.NU_DOCU_BANC), Max(T1.DE_OBSE)        
From    TCMOVI_BANC T1, TTDOCU_CNTB T2        
Where   T1.CO_EMPR = @ISCO_EMPR        
 And T1.CO_BANC = @ISCO_BANC        
 And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
 And T1.TI_SITU != 'ANU'         
 And T1.CO_TIPO_DOCU = T2.TI_DOCU        
 And (        
           ( T1.SI_OPER_CONC = 'N' And (T1.AA_BNCO < @ISAA_CONC Or (T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO <= @ISMM_CONC )))        
           Or (  T1.AA_CONC = @ISAA_CONC And T1.MM_CONC = @ISMM_CONC )        
           Or (  T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO = @ISMM_CONC )        
              )        
 And (T1.CO_GRUP_CONC IS NOT NULL And T1.CO_GRUP_CONC  != '')
 And Len(Rtrim(Ltrim(isnull(t1.NU_UPDA_MASI,'')))) = 0            
Group By T1.CO_EMPR , T1.CO_BANC, T1.NU_CNTA_BANC , T1.CO_GRUP_CONC        
UNION ALL        
Select  @ISCO_EMPR, @ISCO_BANC, @ISNU_CNTA_BANC, @ISAA_CONC, @ISMM_CONC, T1.FE_OPER, T1.NU_COMP_BANC, T1.NU_CORR_CONC, T1.TI_MOVI_BANC,       
 
	  T1.IM_MOVI, T1.AA_BNCO, T1.MM_BNCO, NULL, T1.CO_TIPO_DOCU, T1.SI_OPER_CONC, T2.CO_SUNA,         
	  T1.NU_DOCU_BANC, T1.DE_OBSE        
From  TCMOVI_BANC T1, TTDOCU_CNTB T2        
Where   T1.CO_EMPR = @ISCO_EMPR        
 And T1.CO_BANC = @ISCO_BANC        
 And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
 And T1.TI_SITU != 'ANU'        
 And T1.CO_TIPO_DOCU = T2.TI_DOCU        
         
 And (        
           ( T1.SI_OPER_CONC = 'N'  And (T1.AA_BNCO < @ISAA_CONC Or (T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO <= @ISMM_CONC )))        
           Or (  T1.AA_CONC = @ISAA_CONC And T1.MM_CONC = @ISMM_CONC )        
           Or (  T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO = @ISMM_CONC )        
              )        
 And ( T1.CO_GRUP_CONC IS NULL Or T1.CO_GRUP_CONC  = '' )

UNION ALL        
Select  @ISCO_EMPR, @ISCO_BANC, @ISNU_CNTA_BANC, @ISAA_CONC, @ISMM_CONC, T1.FE_OPER, T1.NU_COMP_BANC, T1.NU_CORR_CONC, T1.TI_MOVI_BANC,       
 
	  T1.IM_MOVI, T1.AA_BNCO, T1.MM_BNCO, NULL, T1.CO_TIPO_DOCU, T1.SI_OPER_CONC, T2.CO_SUNA,         
	  T1.NU_DOCU_BANC, T1.DE_OBSE        
From  TCMOVI_BANC T1, TTDOCU_CNTB T2        
Where   T1.CO_EMPR = @ISCO_EMPR        
 And T1.CO_BANC = @ISCO_BANC        
 And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
 And T1.TI_SITU != 'ANU'        
 And T1.CO_TIPO_DOCU = T2.TI_DOCU        
         
 And (        
           ( T1.SI_OPER_CONC = 'N'  And (T1.AA_BNCO < @ISAA_CONC Or (T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO <= @ISMM_CONC )))        
           Or (  T1.AA_CONC = @ISAA_CONC And T1.MM_CONC = @ISMM_CONC )        
           Or (  T1.AA_BNCO = @ISAA_CONC And T1.MM_BNCO = @ISMM_CONC )        
              )        
 And (T1.CO_GRUP_CONC IS NOT NULL And T1.CO_GRUP_CONC  != '')
 And Len(Rtrim(Ltrim(isnull(t1.NU_UPDA_MASI,'')))) > 0            

UNION ALL        
Select  @ISCO_EMPR, @ISCO_BANC, @ISNU_CNTA_BANC, @ISAA_CONC, @ISMM_CONC, T1.FE_OPER, T1.NU_COMP_BANC, NULL, T1.TI_MOVI_BANC,        
  T1.IM_MOVI, T1.AA_BNCO, T1.MM_BNCO, NULL, T1.CO_TIPO_DOCU, T1.ST_CONC, T2.CO_SUNA,         
  T1.NU_DOCU_BANC, T1.DE_NOMB_ORDE        
From  TCMOVI_BACO T1, TTDOCU_CNTB T2        
Where   T1.CO_EMPR = @ISCO_EMPR        
 And T1.CO_BANC = @ISCO_BANC        
 And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
 And (T1.AA_CONC < @ISAA_CONC  Or (T1.AA_CONC = @ISAA_CONC And T1.MM_CONC <= @ISMM_CONC))        
 And T1.ST_CONC = 'N'        
 And T1.ST_FLAG = 'S'        
 And T1.CO_TIPO_DOCU = T2.TI_DOCU        
 And Not Exists (Select  t2.CO_EMPR
   From  TCMOVI_BACO t2        
   Where    T1.CO_EMPR = t2.CO_EMPR        
    And T1.CO_BANC = t2.CO_BANC        
    And T1.NU_CNTA_BANC = t2.NU_CNTA_BANC        
    And  (t2.AA_CONC < @ISAA_CONC        
    Or   (t2.AA_CONC = @ISAA_CONC        
    And t2.MM_CONC <= @ISMM_CONC))        
    And T1.TI_MOVI_BANC = t2.TI_MOVI_BANC        
    And T1.NU_COMP_BANC = t2.NU_COMP_BANC        
    And T1.CO_TIPO_DOCU = t2.CO_TIPO_DOCU        
    And T1.NU_DOCU_BANC = t2.NU_DOCU_BANC        
    And t2.ST_CONC = 'S' )        
UNION ALL        
Select  @ISCO_EMPR, @ISCO_BANC, @ISNU_CNTA_BANC, @ISAA_CONC, @ISMM_CONC, T1.FE_OPER, T1.NU_COMP_BANC, NULL, T1.TI_MOVI_BANC,        
  T1.IM_MOVI, T1.AA_BNCO, T1.MM_BNCO, NULL, T1.CO_TIPO_DOCU, T1.ST_CONC, T2.CO_SUNA,         
 T1.NU_DOCU_BANC, T1.DE_NOMB_ORDE        
From  TCMOVI_BACO T1, TTDOCU_CNTB T2        
Where   T1.CO_EMPR = @ISCO_EMPR        
 And T1.CO_BANC = @ISCO_BANC        
 And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
 And T1.AA_CONC = @ISAA_CONC        
 And T1.MM_CONC = @ISMM_CONC        
 And (T1.ST_CONC = 'S'  Or T1.ST_CONC Is Null)        
 And T1.ST_FLAG = 'S'        
 And T1.CO_TIPO_DOCU = T2.TI_DOCU           
        
        
        
Declare CU_TCMOVI_BACO_Q01_1 Cursor        
For        
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_CONC, T1.MM_CONC, T1.CO_TIPO_DOCU,        
  T1.SI_OPER_CONC, T1.CO_SUNA, T1.TI_MOVI_BANC, T1.IM_MOVI, T1.NU_CORR_CONC        
From #TWMOVI_BACO T1        
        
        
Open  CU_TCMOVI_BACO_Q01_1        
Fetch CU_TCMOVI_BACO_Q01_1 Into        
@CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @CSAA_CONC, @CSMM_CONC, @CSCO_TIPO_DOCU,        
@CSSI_OPER_CONC, @CSCO_SUNA, @CSTI_MOVI_BANC, @CNIM_MOVI, @CSNU_CORR_CONC        
        
Select @VNNU_FETC_0001 = @@FETCH_STATUS        
        
While (@VNNU_FETC_0001 = 0)        
  Begin        
    If (@VNNU_FETC_0001  < 0)        
       Begin        
    --RAISERROR 20001 "FIN DE ARCHIVO"        
    CLOSE CU_TCMOVI_BACO_Q01_1        
    DEALLOCATE CU_TCMOVI_BACO_Q01_1        
    RETURN         
        End        
        
   If @CSSI_OPER_CONC = 'S'        
     Begin        
		 Update #TWMOVI_BACO        
		 Set SI_OPER_0001 = T2.SI_OPER_CONC,        
		     FE_OPER_0001 = T2.FE_OPER,        
		     CO_TIPO_0001 = T2.CO_TIPO_DOCU,        
		     NU_CORR_0001 = T2.NU_CORR_CONC,        
		     CO_SUNA_0001 = T2.CO_SUNA,        
		     AA_BNCO_0001 = T2.AA_BNCO,        
		     MM_BNCO_0001 = T2.MM_BNCO,        
		     TI_MOVI_0001 = T2.TI_MOVI_BANC,        
		     IM_MOVI_0001 = T2.IM_MOVI,        
		     DE_OBSE_0001 = T2.DE_OBSE        
		 From #TWMOVI_BACO T1, TWCONC_BANC T2        
		 Where T1.CO_EMPR = @CSCO_EMPR         
		 And T1.CO_BANC = @CSCO_BANC         
		 And T1.NU_CNTA_BANC = @CSNU_CNTA_BANC         
		 And T1.AA_CONC = @CSAA_CONC         
		 And T1.MM_CONC = @CSMM_CONC         
		 And T1.CO_SUNA = @CSCO_SUNA        ---
		 And T1.NU_CORR_CONC = @CSNU_CORR_CONC        
		 And T1.NU_CORR_CONC = T2.NU_CORR_CONC        ---
		 And T1.CO_SUNA = T2.CO_SUNA    ---    
		 And T1.TI_MOVI_BANC = T2.TI_MOVI_BANC        
		 And T1.IM_MOVI = T2.IM_MOVI        
        
 IF (@@ERROR != 0)        
       BEGIN        
       CLOSE CU_TCMOVI_BACO_Q01_1        
       DEALLOCATE CU_TCMOVI_BACO_Q01_1        
       RETURN        
    END        
     End        
        
   Fetch CU_TCMOVI_BACO_Q01_1 Into        
   @CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @CSAA_CONC, @CSMM_CONC, @CSCO_TIPO_DOCU,        
   @CSSI_OPER_CONC, @CSCO_SUNA, @CSTI_MOVI_BANC, @CNIM_MOVI, @CSNU_CORR_CONC        
        
  SELECT @VNNU_FETC_0001 = @@FETCH_STATUS        
        
END        
        
CLOSE CU_TCMOVI_BACO_Q01_1        
DEALLOCATE CU_TCMOVI_BACO_Q01_1        


SELECT CO_BANC, NU_CNTA_BANC, AA_CONC, MM_CONC, SI_OPER_CONC, FE_OPER, NU_COMP_BANC,         
 TI_MOVI_BANC, CO_TIPO_DOCU, NU_DOCU_BANC, NU_CORR_CONC, CO_SUNA, AA_BNCO, MM_BNCO,         
 CO_GRUP_CONC, IM_MOVI, SI_OPER_0001, TI_MOVI_0001, FE_OPER_0001, CO_TIPO_0001,         
 CO_SUNA_0001, AA_BNCO_0001, MM_BNCO_0001, IM_MOVI_0001, NU_CORR_0001, DE_OBSE, DE_OBSE_0001,        
 'A'        
FROM #TWMOVI_BACO        
UNION ALL         
SELECT CO_BANC, NU_CNTA_BANC, NULL, NULL, NULL, FE_OPER, NULL,         
 NULL, NULL, NULL, NULL, NULL, NULL, NULL,        
 NULL, NULL, SI_OPER_CONC, TI_MOVI_BANC, FE_OPER, CO_TIPO_DOCU,         
 CO_SUNA, AA_BNCO, MM_BNCO, IM_MOVI, NU_CORR_CONC, '', DE_OBSE,        
 'B'        
FROM TWCONC_BANC        
WHERE CO_EMPR = @ISCO_EMPR        
And CO_BANC = @ISCO_BANC        
And NU_CNTA_BANC = @ISNU_CNTA_BANC        
And AA_CONC = @ISAA_CONC        
And MM_CONC = @ISMM_CONC        
And SI_OPER_CONC = 'N'        
      
UNION ALL         
SELECT T1.CO_BANC, T1.NU_CNTA_BANC, NULL, NULL, NULL, T1.FE_OPER, NULL,         
 NULL, NULL, NULL, NULL, NULL, NULL, NULL,        
 NULL, NULL, T1.ST_CONC, T1.TI_MOVI_BANC, T1.FE_OPER, T1.CO_TIPO_DOCU,         
 T2.CO_SUNA, T1.AA_BNCO, T1.MM_BNCO, T1.IM_MOVI, T1.NU_CORR_CONC, '', T1.DE_NOMB_ORDE,        
 'C'        
FROM TCMOVI_BACO T1, TTDOCU_CNTB T2        
WHERE T1.CO_EMPR = @ISCO_EMPR        
And T1.CO_BANC = @ISCO_BANC        
And T1.NU_CNTA_BANC = @ISNU_CNTA_BANC        
And (T1.AA_CONC < @ISAA_CONC Or (T1.AA_CONC = @ISAA_CONC And T1.MM_CONC <= @ISMM_CONC))        
And T1.ST_ORIG = 'B'        
And T1.ST_CONC = 'N'        
And T1.CO_TIPO_DOCU = T2.TI_DOCU       
ORDER BY  5, 6, 7, 12, 11

        
RETURN


GO
